#!/usr/bin/python3
# -*- coding: UTF-8 -*-

import pandas as pd
import openpyxl
import openpyxl.utils.dataframe as dataframe_utils
import sys
import math
import os


def try_file_path(file_path):
    tmp_path = file_path
    (root, ext) = os.path.splitext(file_path)
    i = 1
    while os.path.exists(tmp_path):
        tmp_path = f"{root}({i}){ext}"
        i += 1

    return tmp_path


class Expect:
    def __init__(self, file_path):
        self.file_path = file_path
        names = [
            "A",
            "B",
            "C",
            "D",
            "E",
            "F",
            "G",
            "H",
            "I",
            "J",
            "K",
            "L",
            "M",
            "N",
            "O",
            "P",
            "Q",
            "R",
            "S",
            "T",
            "U",
            "V",
            "W",
            "X",
            "Y",
            "Z",
            "AA",
            "AB",
        ]
        self.df = pd.read_excel(file_path, sheet_name=0, header=3, names=names)

    def export(self, df, target_file_path):
        rows = dataframe_utils.dataframe_to_rows(df, index=True)
        """输出dataframe样式的表格"""
        wb = openpyxl.Workbook()
        ws = wb.active

        for r in rows:
            ws.append(r)

        for cell in ws["A"] + ws[1]:
            cell.style = "Pandas"

        wb.save(target_file_path)


def fix_val(val, decimal=2):
    return round(val, decimal)


def percent(val):
    return f"{val*100:.2f}%"


def sum_from_one(df):
    this_year_from_one = fix_val(df["B"].sum())
    df["TA"] = df["B"] / (1 + df["C"])
    last_year_from_one = fix_val(df["TA"].sum())
    df["TB"] = (df["B"] - df["TA"]) / df["TA"]
    per1 = (this_year_from_one - last_year_from_one) / last_year_from_one
    print(this_year_from_one, last_year_from_one, per1)
    this_year_from_one_jianan = fix_val(df["D"].sum())
    df["TC"] = df["D"] / (1 + df["E"])
    last_year_from_one_jianan = fix_val(df["TC"].sum())
    df["TD"] = (df["D"] - df["TC"]) / df["TC"]
    per2 = (
        this_year_from_one_jianan - last_year_from_one_jianan
    ) / last_year_from_one_jianan
    print(this_year_from_one_jianan, last_year_from_one_jianan, per2)
    return (this_year_from_one, percent(per1), this_year_from_one_jianan, percent(per2))


def sum_month(df):
    this_year_month = fix_val(df["F"].sum())
    df["TE"] = df["F"] / (1 + df["G"])
    df["TE"] = df["TE"].fillna(0)
    last_year_month = fix_val(df["TE"].sum())
    df["TF"] = (df["F"] - df["TE"]) / df["TE"]
    per1 = (this_year_month - last_year_month) / last_year_month
    print(this_year_month, last_year_month, per1)
    this_year_month_jianan = fix_val(df["H"].sum())
    df["TG"] = df["H"] / (1 + df["I"])
    df["TG"] = df["TG"].fillna(0)
    last_year_month_jianan = fix_val(df["TG"].sum())
    try:
        df["TH"] = (df["H"] - df["TG"]) / df["TG"]
    except ZeroDivisionError:
        print("TG is zero")
    per2 = (this_year_month_jianan - last_year_month_jianan) / last_year_month_jianan
    print(this_year_month_jianan, last_year_month_jianan, per2)
    return (this_year_month, percent(per1), this_year_month_jianan, percent(per2))


def sum_project(df):
    project_count = df["J"].sum()
    total_invest = df["K"].sum()
    leave_invest = df["L"].sum()

    has_number = df["M"].sum()
    has_number_leave = df["N"].sum()
    bigger_5 = df["O"].sum()

    month_expect = df["P"].sum()
    from_one_expect = df["Q"].sum()

    return (
        math.ceil(project_count),
        fix_val(total_invest),
        fix_val(leave_invest),
        has_number,
        fix_val(has_number_leave),
        bigger_5,
        fix_val(month_expect),
        fix_val(from_one_expect),
    )


def sum_new_project(df):
    project_count = df["R"].sum()
    total_invest = df["S"].sum()
    month_expect = df["T"].sum()
    from_one_expect = df["U"].sum()

    return (
        math.ceil(project_count),
        fix_val(total_invest),
        fix_val(month_expect),
        fix_val(from_one_expect),
    )


def sum_into_project(df):
    project_count = df["V"].sum()
    total_invest = df["W"].sum()

    return (
        math.ceil(project_count),
        fix_val(total_invest),
    )


def main():
    if len(sys.argv) != 3:
        print("""

python analsis.py <data_file.xlsx> <month>
""")

    exp = Expect(sys.argv[1])
    month = sys.argv[2]

    adf = exp.df.drop(exp.df.index[10:], axis=0)
    exp.export(adf, try_file_path("target.xlsx"))

    (this_year_from_one, per1, this_year_from_one_jianan, per2) = sum_from_one(adf)

    (this_year_month, per3, this_year_month_jianan, per4) = sum_month(adf)

    str_tip = f"""1—{month}月，玉溪市能源以外工业投资预计完成{this_year_from_one}亿元、同比增长{per1}，能源外工业建安投资预计完成{this_year_from_one_jianan}亿元、同比增长{per2}。其中，{month}月当月能源以外工业投资预计完成{this_year_month}亿元、同比增长{per3}，能源外工业建安投资预计完成{this_year_month_jianan}亿元、同比增长{per4}。"""
    print("----------------------------")

    print(str_tip)
    print("----------------------------")

    (
        project_count,
        total_invest,
        leave_invest,
        has_number,
        has_number_leave,
        bigger_5,
        month_expect,
        from_one_expect,
    ) = sum_project(adf)

    在库项目 = f"""在库项目{project_count}个，总投资{total_invest}亿元、剩余投资{leave_invest}亿元，可报数项目{has_number}个、剩余投资{has_number_leave}亿元，其中总投资5亿元以上项目{bigger_5}个。{month}月当月预计完成投资{month_expect}亿元，1—{month}月预计完成投资{from_one_expect}亿元。"""

    print(在库项目)
    print("----------------------------")

    (project_count_new, total_invest_new, month_expect_new, from_one_expect_new) = (
        sum_new_project(adf)
    )

    新增项目 = f"""{month}月当月，通过审核预计新增入库项目{project_count_new}个，总投资{total_invest_new}亿元。{month}月当月预计完成投资{month_expect_new}亿元，1—{month}月预计完成投资{from_one_expect_new}亿元。"""

    print(新增项目)
    print("----------------------------")

    (project_count_into, total_invest_into) = sum_into_project(adf)
    未入库 = f"""共完成储备项目{project_count_into}个，总投资{total_invest_into}亿元"""

    print(未入库)
    print("----------------------------")


if __name__ == "__main__":
    main()
